﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsApplication1
{
    public partial class Form6 : Form
    {
        public Form6()
        {
            InitializeComponent();
        }

        SqlConnection conn;
        SqlCommand com;
        SqlDataAdapter da;
        DataSet ds;
        public static string s;
        SqlDataReader dr;

        void showfil()
        {
            conn = new SqlConnection();
            conn.ConnectionString =SQL.getCon;
            com = new SqlCommand();
            com.CommandText = "select xh,mc,gg,txsl,sjsl,zy from ypsfjl,ypgg where ypsfjl.ypid=ypgg.ypid and no='" + s + "'";
            com.Connection = conn;
            da = new SqlDataAdapter();
            ds = new DataSet();
            da.SelectCommand = com;
            da.Fill(ds, "ggml");
            dataGridView2.DataSource = ds.Tables["ggml"];
        }

        private void button1_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            conn.ConnectionString =SQL.getCon;
            com = new SqlCommand();
            if (checkBox1.Checked)
            { com.CommandText = "select distinct(no),mc,pc,ph,scrq,xq,zy,tzr,tzrq from ypsfjl,ypgg where ypsfjl.ypid=ypgg.ypid and dj=2 and tzrq between '" + dateTimePicker1.Text + "'and '" + dateTimePicker2.Text + "'"; }
            else
                com.CommandText = "select distinct(no),mc,pc,ph,scrq,xq,zy,tzr,tzrq from ypsfjl,ypgg where ypsfjl.ypid=ypgg.ypid and dj=2 and tzrq between '" + dateTimePicker1.Text + "'and '" + dateTimePicker2.Text + "' and shr is null";
            com.Connection = conn;
            da = new SqlDataAdapter();
            ds = new DataSet();
            da.SelectCommand = com;
            da.Fill(ds, "ypjl");
            dataGridView1.DataSource = ds.Tables["ypjl"];
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            s = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString();
            showfil();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            conn.ConnectionString =SQL.getCon;
            com = new SqlCommand();
            com.Connection = conn;
            com.CommandText = "delete ypsfjl where no='" + s + "'";
            conn.Open();
            com.ExecuteNonQuery();
            conn.Close();
            showfil();
        }

        private void button5_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection();
            conn.ConnectionString =SQL.getCon;
            com = new SqlCommand();
            com.Connection = conn;
            com.CommandText = "update ypsfjl set shr=@shr,shrq=@shrq where no='" + s + "'";
            SqlParameter shr = new SqlParameter("shr", SqlDbType.NChar, 20);
            shr.Value = textBox1.Text;
            com.Parameters.Add(shr);
            SqlParameter shrq = new SqlParameter("shrq", SqlDbType.DateTime, 10);
            shrq.Value = dateTimePicker1.Text;
            com.Parameters.Add(shrq);
            conn.Open();
            com.ExecuteNonQuery();
            conn.Close();
            com.CommandText = "select kfid,ypid,pc,xq,txsl,sjsl from ypsfjl where no='" + s + "'";
            da = new SqlDataAdapter();
            da.SelectCommand = com;
            ds = new DataSet();
            da.Fill(ds, "yptj");
            int i;
            int i1;
            conn.Open();
            com.CommandText = "select max(xh) as id from ypsfjl where no='" + s + "'";
            dr = com.ExecuteReader();
            dr.Read();
            string s2;
            s2 = dr["id"].ToString();
            i1 = int.Parse(s2);
            conn.Close();
            SqlParameter kfid = new SqlParameter("kfid", SqlDbType.Int, 10);
            SqlParameter ypid = new SqlParameter("ypid", SqlDbType.Int, 10);
            SqlParameter pc = new SqlParameter("pc", SqlDbType.NVarChar, 10);
            SqlParameter xq = new SqlParameter("xq", SqlDbType.NVarChar, 10);
            SqlParameter kysl = new SqlParameter("kysl", SqlDbType.Int, 10);
            SqlParameter sjsl = new SqlParameter("sjsl", SqlDbType.Int, 10);
            //SqlParameter sjje = new SqlParameter("sjje", SqlDbType.Float, 10);
            //SqlParameter sjcj = new SqlParameter("sjcj", SqlDbType.Float, 10);
            //SqlParameter lsj = new SqlParameter("lsj", SqlDbType.Float, 10);
            com.Parameters.Add(kfid);
            com.Parameters.Add(ypid);
            com.Parameters.Add(pc);
            com.Parameters.Add(xq);
            com.Parameters.Add(kysl);
            com.Parameters.Add(sjsl);
            //com.Parameters.Add(sjje);
            //com.Parameters.Add(sjcj);
            //com.Parameters.Add(lsj);
            for (i = 0; i < i1; i++)
            {
                conn.Open();
                com.CommandText = "insert ypkc(kfid,ypid,pc,xq,kysl,sjsl) values(@kfid,@ypid,@pc,@xq,@kysl,@sjsl)";
                //SqlParameter kfid = new SqlParameter("kfid", SqlDbType.Int, 10);
                kfid.Value = ds.Tables["yptj"].Rows[i]["kfid"].ToString();
                //com.Parameters.Add(kfid);
                //SqlParameter ypid = new SqlParameter("ypid", SqlDbType.Int, 10);
                ypid.Value = ds.Tables["yptj"].Rows[i]["ypid"].ToString();
                //com.Parameters.Add(ypid);
                //SqlParameter pc = new SqlParameter("pc", SqlDbType.NVarChar , 10);
                pc.Value = ds.Tables["yptj"].Rows[i]["pc"].ToString();
                //com.Parameters.Add(pc);
                //SqlParameter xq = new SqlParameter("xq", SqlDbType.NVarChar , 10);
                xq.Value = ds.Tables["yptj"].Rows[i]["xq"].ToString();
                //com.Parameters.Add(xq);
                //SqlParameter kysl = new SqlParameter("kysl", SqlDbType.Int, 10);
                kysl.Value = ds.Tables["yptj"].Rows[i]["txsl"].ToString();
                //com.Parameters.Add(kysl);
                //SqlParameter sjsl = new SqlParameter("sjsl", SqlDbType.Int, 10);
                sjsl.Value = ds.Tables["yptj"].Rows[i]["sjsl"].ToString();
                com.ExecuteNonQuery();
                conn.Close();
            }
        }
    }
}